package com.wiserp.rest.helper;

import java.math.BigDecimal;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.wiserp.rest.model.DlvOrder;
import com.wiserp.rest.model.DlvOrderItem;
import com.wiserp.rest.model.DlvOrderItemPick;
import com.wiserp.rest.model.OpResult;

public class DlvOrderHelper extends JdbcDaoSupport implements INounHelper {
	private static final String SQL_GET_UNPOSTED_DLVORDER = "SELECT a.faa001,a.faa002,a.faa003,faa043,b.bda002 FROM sfa01 a LEFT JOIN sae01 b ON(a.faa003=b.bda001) WHERE a.faa053='4' AND a.faa006='3a' AND a.faa100='a'";	
	private static final String SQL_GET_UNPOSTED_COUNT_DLVORDER = "SELECT COUNT(*) FROM sfa01 WHERE faa006='3a' AND faa053='4' AND faa100='a' ";
	private static final String SQL_GET_DLVORDERITEM_BY_FLOWNO = "SELECT fab002,fab004,fab006,fab069 FROM sfa02 WHERE fab001=?";
	private static final String SQL_GET_DLVORDER_BY_FLOWNO = "SELECT a.faa001,a.faa002,a.faa003,faa043,b.bda002 FROM sfa01 a LEFT JOIN sae01 b ON(a.faa003=b.bda001) WHERE a.faa001=? AND a.faa053='4' AND a.faa100='a'";
	private PlatformTransactionManager txManager;
	
	public void setTxManager(PlatformTransactionManager txManager) {
		this.txManager = txManager;
	}
	
	public int getUnpostCount() {
		return this.getJdbcTemplate().queryForInt(
				SQL_GET_UNPOSTED_COUNT_DLVORDER);
	}
	public List findUnpost() {
		return this.getJdbcTemplate().query(SQL_GET_UNPOSTED_DLVORDER,
				new RowMapper<DlvOrder>() {
					public DlvOrder mapRow(ResultSet rs, int i)
							throws SQLException {
						DlvOrder data = new DlvOrder();
						data.setFlowNo(rs.getString("faa001"));
						data.setCustNo(rs.getString("faa003"));
						data.setCustName(rs.getString("bda002"));
						data.setBillDate(rs.getDate("faa002"));
						return data;
					}
				});
	}

	
	public Object getByFlowNo(String flowNo) {
		Object result = null;
		try{
			result = this.getJdbcTemplate().queryForObject(SQL_GET_DLVORDER_BY_FLOWNO,
				new RowMapper<DlvOrder>() {
					public DlvOrder mapRow(ResultSet rs, int i)
							throws SQLException {
						DlvOrder data = new DlvOrder();
						data.setFlowNo(rs.getString("faa001"));
						data.setCustNo(rs.getString("faa003"));
						data.setCustName(rs.getString("bda002"));
						data.setBillDate(rs.getDate("faa002"));
						data.setItems(findItems(data.getFlowNo()));
						return data;
					}
				}, flowNo);
		}
		catch(EmptyResultDataAccessException e){ 
			result = OpResult.FAIL(String.format("单据(%s)不存在或者已过账或者已经上传过！", flowNo), 1);
		}
		return result;
	}
	
	public List<DlvOrderItem> findItems(String flowNo){
		return this.getJdbcTemplate().query(SQL_GET_DLVORDERITEM_BY_FLOWNO,
				new RowMapper<DlvOrderItem>() {
					public DlvOrderItem mapRow(ResultSet rs, int i)
							throws SQLException {
						DlvOrderItem data = new DlvOrderItem();
						data.setItemNo(rs.getInt("fab002"));
						data.setProdNo(rs.getString("fab004"));
						data.setQty(rs.getDouble("fab006"));
						data.setAllowedBatchNo(rs.getString("fab069"));
						data.setQtyInLoc(CommonDBUtil.findQtyInLocByProdNoAndBatchNo(data.getProdNo(), data.getAllowedBatchNo(), DlvOrderHelper.this.getJdbcTemplate()));
						
						return data;
					}
				}, flowNo);
	}
	
	public OpResult updateInfo(final Object bill){
		OpResult result = null;
		DefaultTransactionDefinition def = new DefaultTransactionDefinition();
		TransactionStatus status = txManager.getTransaction(def);
		try{
			result = updateInfoInternal(bill);
			txManager.commit(status);
		}catch(Exception ex){
			ex.printStackTrace();
			txManager.rollback(status);
			result = OpResult.UNKNOWNFAIL;
		}
		return result;
	}
	
	public OpResult updateInfoInternal(final Object bill){	
		DlvOrder data = (DlvOrder)bill;
		Object result = getByFlowNo(data.getFlowNo());
		if(result instanceof OpResult)
			return (OpResult)result;
		
		DlvOrder dataDB = (DlvOrder)result;
		StringBuffer msg = new StringBuffer("");
		for(DlvOrderItem item: data.getItems()){
			boolean found = false;
			for(DlvOrderItem itemDB:dataDB.getItems()){
				if(item.getItemNo() == itemDB.getItemNo() && item.getProdNo().equals(itemDB.getProdNo())){
					found = true;
					break;
				}
			}
			
			if(!found)
				msg.append(String.format(",项目编号(%d)物料编号为(%s)的项目在ERP中不存在", item.getItemNo(),item.getProdNo()));
		}
		
		if(msg.length()>0){
			msg.insert(0, String.format("上传的单据(%s)中", data.getFlowNo())).append(",请检查");
			return OpResult.FAIL(msg.toString(), 2);
		}
		
		for(DlvOrderItem itemDB:dataDB.getItems()){
			boolean found = false;
			for(DlvOrderItem item:data.getItems()){
				if(item.getItemNo() == itemDB.getItemNo() && item.getProdNo().equals(itemDB.getProdNo())){
					found = true;
					break;
				}
			}
			
			if(!found)
				msg.append(String.format(",项目编号(%d)物料编号为(%s)的项目没有更新", itemDB.getItemNo(),itemDB.getProdNo()));
		}
		
		if(msg.length()>0){
			msg.insert(0, String.format("ERP的单据(%s)中", dataDB.getFlowNo())).append(",请检查");
			return OpResult.FAIL(msg.toString(), 3);
		}
		
		for(DlvOrderItem item:data.getItems()){
			boolean found = false;
			for(DlvOrderItem itemDB:dataDB.getItems()){
				if(item.getItemNo() == itemDB.getItemNo() && item.getProdNo().equals(itemDB.getProdNo())){
					double pickedQty = 0.0;
					if(item.getPickedItems()!=null)
						for(DlvOrderItemPick pickItem:item.getPickedItems())
							pickedQty += pickItem.getPickQty();
					if(pickedQty > itemDB.getQty()){
						found = true;
						break;
					}else
						item.setPickedQty(pickedQty);
				}
			}
			
			if(found)
				msg.append(String.format(",项目编号(%d)物料编号为(%s)的项目的捡货数量大于出货数量", item.getItemNo(),item.getProdNo()));
		}
		
		if(msg.length()>0){
			msg.insert(0, String.format("上传的单据(%s)中", dataDB.getFlowNo())).append(",请检查");
			return OpResult.FAIL(msg.toString(), 4);
		}
		
		if(StringUtils.isNotEmpty(data.getOperatorNo()))
			this.getJdbcTemplate().update("UPDATE sfa01 SET faa055=?,faa100='1' WHERE faa001=?",data.getOperatorNo(),data.getFlowNo());
		else
			this.getJdbcTemplate().update("UPDATE sfa01 SET faa100='1' WHERE faa001=?",data.getFlowNo());
		
		saveDlvOrderPick(data);
		
		return OpResult.SUCCESS;
		
	}
	
	private String getParentWhNo(String whLocNo){
		List<String> l =this.getJdbcTemplate().queryForList("SELECT haa012 FROM sha01 WHERE haa001=?", String.class,whLocNo);
		if(l!=null && l.size()>0)
			return l.get(0);
		else
			return "";
	}	
	
	private void saveDlvOrderPick(final DlvOrder data){
		for(final DlvOrderItem item:data.getItems()){
//			final Map<String, Object> map = this.getJdbcTemplate().queryForMap("SELECT a.fab011,b.bef007,c.hcd010,c.hcd011,c.hcd012 FROM sfa02 a " +
//					"LEFT JOIN saf06 b ON(a.fab004=b.bef001) " +
//					"LEFT JOIN shc04 c ON(a.fab062=c.hcd002 AND a.fab004=c.hcd003 AND a.fab019=c.hcd006 AND a.fab020=c.hcd007 AND c.hcd013>0)" +
//					"WHERE a.fab001=? AND a.fab002=?",data.getFlowNo(),item.getItemNo());
			
			final Map<String, Object> map = this.getJdbcTemplate().queryForMap("SELECT a.fab011,b.bef007 FROM sfa02 a " +
					"LEFT JOIN saf06 b ON(a.fab004=b.bef001) " +
					"WHERE a.fab001=? AND a.fab002=?",data.getFlowNo(),item.getItemNo());
			
			if(item.getPickedItems()!=null){
				int i=0;
				
				for(final DlvOrderItemPick pickItem:item.getPickedItems()){
					
					List<Map<String,Object>> batchList = getJdbcTemplate().queryForList("SELECT hcc007,hcc008,hcc009,hcc010 FROM shc03 WHERE hcc003=? AND hcc004=? AND hcc019=? ORDER BY hcc008",item.getProdNo(),pickItem.getWhLocNo(),pickItem.getBatchNo());
					if(batchList!=null && batchList.size()>0){
						double needFillQty = pickItem.getPickQty();
						for(Map<String,Object> arow:batchList){
							Double qty = ((BigDecimal)arow.get("hcc010")).doubleValue();
							map.put("hcd010", arow.get("hcc007"));
							map.put("hcd011", arow.get("hcc008"));
							map.put("hcd012", arow.get("hcc009"));
							if(needFillQty>qty){//can not fill all
								pickItem.setPickQty(qty);
								needFillQty-=qty;
								
							}else{
								pickItem.setPickQty(needFillQty);
								needFillQty = 0.0;
							}

							pickItem.setItemNo((i+1)*10);
							this.getJdbcTemplate().update("INSERT INTO sfa05(fae001,fae002,fae003,fae004,fae005,fae006,fae010,fae011,fae012,fae013,fae016,fae017,fae018,fae019,fae020,fae021,fae032)" +
									   "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", 
									   new PreparedStatementSetter(){		
								public void setValues(PreparedStatement ps)
										throws SQLException {
									ps.setString(1, data.getFlowNo());
									ps.setInt(2, item.getItemNo());
									ps.setInt(3, pickItem.getItemNo());
									ps.setString(4, "1");	//库存捡货
									ps.setString(5, item.getProdNo());
									ps.setString(6,pickItem.getWhLocNo());
									ps.setDouble(7, pickItem.getPickQty());
									Date dt = new Date(System.currentTimeMillis());
									if(pickItem.getPickDate()!=null)
										dt = new Date(pickItem.getPickDate().getTime());
									ps.setDate(8, dt);
									ps.setString(9, pickItem.getBatchNo());
									ps.setString(10, item.getProdNo());
									ps.setString(11, (String)map.get("fab011"));
									ps.setString(12, (String)map.get("hcd010"));
									ps.setInt(13, (Integer)map.get("hcd011"));
									ps.setString(14, (String)map.get("hcd012"));
									ps.setString(15, "1");//可用库存
									ps.setString(16, (String)map.get("bef007"));
									ps.setString(17, getParentWhNo(pickItem.getWhLocNo()));
									
								}
								
							});	
							
							i++;
							if(needFillQty==0.0)
								break;
							
							
						}
					}
					
					
				}
			}
			
//			if(item.getPickedItems()!=null){ 
//				this.getJdbcTemplate().batchUpdate("INSERT INTO sfa05(fae001,fae002,fae003,fae004,fae005,fae006,fae010,fae011,fae012,fae013,fae016,fae017,fae018,fae019,fae020,fae021,fae032)" +
//												   "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
//						new BatchPreparedStatementSetter() {
//					public void setValues(PreparedStatement ps, int i) throws SQLException {
//						DlvOrderItemPick pickItem = item.getPickedItems().get(i);
//						ps.setString(1, data.getFlowNo());
//						ps.setInt(2, item.getItemNo());
//						ps.setInt(3, (i+1)*10);
//						ps.setString(4, "1");	//库存捡货
//						ps.setString(5, item.getProdNo());
//						ps.setString(6,pickItem.getWhLocNo());
//						ps.setDouble(7, pickItem.getPickQty());
//						Date dt = new Date(System.currentTimeMillis());
//						if(pickItem.getPickDate()!=null)
//							dt = new Date(pickItem.getPickDate().getTime());
//						ps.setDate(8, dt);
//						ps.setString(9, pickItem.getBatchNo());
//						List<Map<String,Object>> batchList = getJdbcTemplate().queryForList("SELECT hcc007,hcc008,hcc009 FROM shc03 WHERE hcc019=?",pickItem.getBatchNo());
//						if(batchList!=null && batchList.size()>0){
//							map.put("hcd010", batchList.get(0).get("hcc007"));
//							map.put("hcd011", batchList.get(0).get("hcc008"));
//							map.put("hcd012", batchList.get(0).get("hcc009"));
//						}
//						ps.setString(10, item.getProdNo());
//						ps.setString(11, (String)map.get("fab011"));
//						ps.setString(12, (String)map.get("hcd010"));
//						ps.setInt(13, (Integer)map.get("hcd011"));
//						ps.setString(14, (String)map.get("hcd012"));
//						ps.setString(15, "1");//可用库存
//						ps.setString(16, (String)map.get("bef007"));
//						ps.setString(17, getParentWhNo(pickItem.getWhLocNo()));
//					}
//					
//					public int getBatchSize() {
//						return item.getPickedItems().size();
//					}
//				});
//			}
			
			this.getJdbcTemplate().update("UPDATE sfa02 SET fab007=? WHERE fab001=? AND fab002=?", item.getPickedQty(),data.getFlowNo(),item.getItemNo());
			List<Map<String,Object>> l = this.getJdbcTemplate().queryForList("SELECT 1 FROM sfa02 WHERE fab001=? AND (fab007<fab006*(1-fab009) OR fab007>fab006*(1+fab010))",data.getFlowNo());
			
			String smPickingStatus = "";
			if(l!=null && l.size()>0)
				smPickingStatus = "0"; //捡货未完成
			else 
				smPickingStatus = "1"; //捡货完成
			
			this.getJdbcTemplate().update("UPDATE sfa01 SET faa017=? WHERE faa001=?", smPickingStatus,data.getFlowNo());
		}
	}
	
}
